MySQLの暗黙的コミットについて調べて実際に試してみた

sora

2024.10.29

こんにちは、ゲームソリューション部のsoraです。
今回は、MySQLの暗黙的コミットについて調べて実際に試してみたことについて書いていきます。

前提として、MySQLよりPostgreSQLが良いと言いたいわけではありません。
単純に機能としてどう動作するのかを試してみるということが目的となります。

MySQLの暗黙的コミットとは

公式ドキュメントは以下です。

暗黙的コミットがされるものについて、一部を整理して記載します。（全ては記載しません。）

DDLステートメント
- CREATEとDROPについて、TEMPORARYを使っていれば暗黙的コミットはされない
- ⇒ただし、それ自体をロールバックすることもできない（ロールバックしてもテーブルは残る）
- TEMPORARYでない場合、実行前後で暗黙的コミットが入る
トランザクション制御のステートメント
- BEGIN、LOCK TABLES、SET autocommit = 1 (1でない状態から1に変えた場合)、START TRANSACTION、UNLOCK TABLESなどでは暗黙的コミットが入る
- トランザクションのネストはできない
DATA LOAD（ローカルファイルからデータをインポート）で使用するテーブルに対しては暗黙的コミットが入る
管理系のステートメント（ANALYZE TABLEなど）やレプリケーション制御ステートメント（START REPLICAなど）は、暗黙的コミットが入る

今回は、1つ目の DDLステートメント と2つ目の トランザクション制御のステートメント について、実際にAmazon Auroraを使用して動作を確認していきます。
MySQLだけでなく、PostgreSQLではどうなるのかも合わせてみていきます。

DDLステートメントの暗黙的コミット1

MySQL

トランザクションの中で、testデータベースを作成してロールバックをしても、データベースは追加されています。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.04 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

TEMPORARYを付けて一時テーブルを作成してみても、ロールバック時に1 warningとなっており、テーブルは作成されたままになっています。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> USE test;
Database changed

mysql> CREATE TEMPORARY TABLE temp_table (
    ->     id SERIAL PRIMARY KEY,
    ->     data VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DESCRIBE temp_table;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+-----------------+------+-----+---------+----------------+
| id    | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| data  | varchar(255)    | YES  |     | NULL    | NULL           |
+-------+-----------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)

PostgreSQL

トランザクションの中で、testデータベースを作成しようとするとエラーになりました。
当然、ロールバックを実行した後にデータベースを確認すると、データベースは作成されていません。

postgres=> START TRANSACTION;
START TRANSACTION

postgres=*> CREATE DATABASE test;
ERROR:  CREATE DATABASE cannot run inside a transaction block

postgres=!> ROLLBACK;
ROLLBACK

postgres=> \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/rdsadmin          +
           |          |          |             |             |            |                 | rdsadmin=CTc/rdsadmin
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(4 rows)

一時テーブルについては、トランザクションの中で作成はできましたが、ロールバック時に正常にロールバックできていることが確認できました。

postgres=> START TRANSACTION;
START TRANSACTION

postgres=*> CREATE TEMPORARY TABLE temp_table (
    id SERIAL PRIMARY KEY,
    data VARCHAR(255)
);
CREATE TABLE

postgres=*> ROLLBACK;
ROLLBACK

postgres=> \d temp_table
Did not find any relation named "temp_table".

DDLステートメントの暗黙的コミット2

MySQL

テーブルへのINSERTを行いたいため、事前準備としてテーブルを作成します。

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.04 sec)

mysql> USE test;
Database changed

mysql> CREATE TABLE prefectures (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     prefecture VARCHAR(255) NOT NULL,
    ->     prefectural_capital VARCHAR(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> DESCRIBE prefectures;
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int          | NO   | PRI | NULL    | auto_increment |
| prefecture          | varchar(255) | NO   |     | NULL    |                |
| prefectural_capital | varchar(255) | NO   |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

事前準備ができたので、実際に試していきます。
トランザクションの中で、INSERTとCREATE TABLEを実行してみると、INSERT含めて暗黙的コミットがされていました。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO prefectures (
    ->     prefecture,
    ->     prefectural_capital
    -> ) VALUES (
    ->     'Kanagawa',
    ->     'Yokohama'
    -> );
Query OK, 1 row affected (0.02 sec)

mysql> CREATE TABLE dummy (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.62 sec)

mysql> INSERT INTO prefectures (
    ->     prefecture,
    ->     prefectural_capital
    -> ) VALUES (
    ->     'Hyogo',
    ->     'Kobe'
    -> );
Query OK, 1 row affected (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM prefectures;
+----+------------+---------------------+
| id | prefecture | prefectural_capital |
+----+------------+---------------------+
|  1 | Kanagawa   | Yokohama            |
|  2 | Hyogo      | Kobe                |
+----+------------+---------------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE dummy;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.11 sec)

PostgreSQL

PostgreSQLも同様に、事前準備としてテーブルを作成します。

postgres=> CREATE DATABASE test;
CREATE DATABASE

postgres=> \c test
psql (15.8, server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "test" as user "postgres".

test=> CREATE TABLE prefectures (
    id SERIAL PRIMARY KEY,
    prefecture VARCHAR(255) NOT NULL,
    prefectural_capital VARCHAR(255) NOT NULL
);
CREATE TABLE

test=> \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/rdsadmin          +
           |          |          |             |             |            |                 | rdsadmin=CTc/rdsadmin
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(5 rows)

test=> \d prefectures
                                          Table "public.prefectures"
       Column        |          Type          | Collation | Nullable |                 Default
---------------------+------------------------+-----------+----------+-----------------------------------------
 id                  | integer                |           | not null | nextval('prefectures_id_seq'::regclass)
 prefecture          | character varying(255) |           | not null |
 prefectural_capital | character varying(255) |           | not null |
Indexes:
    "prefectures_pkey" PRIMARY KEY, btree (id)

事前準備ができたので、実際に試していきます。
トランザクションの中で、INSERTとCREATE TABLEを実施してもロールバックができていました。

test=> START TRANSACTION;
START TRANSACTION

test=*> INSERT INTO prefectures (
    prefecture,
    prefectural_capital
) VALUES (
    'Kanagawa',
    'Yokohama'
);
INSERT 0 1

test=*> CREATE TABLE dummy (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);
CREATE TABLE

test=*> INSERT INTO prefectures (
    prefecture,
    prefectural_capital
) VALUES (
    'Hyogo',
    'Kobe'
);
INSERT 0 1

test=*> ROLLBACK;
ROLLBACK

test=> SELECT * FROM prefectures;
 id | prefecture | prefectural_capital
----+------------+---------------------
(0 rows)

test=> \d dummy
Did not find any relation named "dummy".

トランザクション制御のステートメントの暗黙的コミット

MySQL

START TRANSACTIONの後に、再度START TRANSACTIONを実行すると暗黙的コミットが実行されます。
その後、INSERTをした後にロールバックをすると、START TRANSACTION後に実行されたクエリはロールバックされています。

ちなみに、直接クエリを実行する部分だけ見るとこんなことはしないと思うかもしれませんが、バックエンドの中で同じコネクションでコミットもロールバックも通らずに、START TRANSACTIONを2回実行してしまうことはあり得るかなと思いました。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO prefectures (
    ->     prefecture,
    ->     prefectural_capital
    -> ) VALUES (
    ->     'Kanagawa',
    ->     'Yokohama'
    -> );
Query OK, 1 row affected (0.01 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO prefectures (
    ->     prefecture,
    ->     prefectural_capital
    -> ) VALUES (
    ->     'Hyogo',
    ->     'Kobe'
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM prefectures;
+----+------------+---------------------+
| id | prefecture | prefectural_capital |
+----+------------+---------------------+
|  1 | Kanagawa   | Yokohama            |
+----+------------+---------------------+
1 row in set (0.00 sec)

PostgreSQL

START TRANSACTIONの後に、再度START TRANSACTIONを実行するとWARNINGが発生します。
その後、ロールバックをすると実行されたクエリは全てロールバックされています。

test=> START TRANSACTION;
START TRANSACTION

test=*> INSERT INTO prefectures (
    prefecture,
    prefectural_capital
) VALUES (
    'Kanagawa',
    'Yokohama'
);
INSERT 0 1

test=*> START TRANSACTION;
WARNING:  there is already a transaction in progress
START TRANSACTION

test=*> INSERT INTO prefectures (
    prefecture,
    prefectural_capital
) VALUES (
    'Hyogo',
    'Kobe'
);
INSERT 0 1

test=*> ROLLBACK;
ROLLBACK

test=> SELECT * FROM prefectures;
 id | prefecture | prefectural_capital
----+------------+---------------------
(0 rows)